import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_基层就业分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，基层就业分布 - 指标计算函数
    
    ## 指标说明
    计算各学历层次及总体基层就业分布情况，包括选调生、西部计划等基层就业类型的占比情况。
    指标基于学生客观表数据，筛选条件为：毕业去向不为待就业和暂不就业，且基层就业字段不为空。
    计算各基层就业类型人数占所有就业人数的比例，并按照学历层次分组统计。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，默认为None
        project_code (Optional[str]): 项目编码，默认为None
        region_code (Optional[str]): 区域编码，默认为None
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "calcVals": [
                    {
                        "val": [
                            {
                                "key": "default",
                                "val": "选调生，占比为0.09%；其次为西部计划（0.04%）"
                            }
                        ]
                    }
                ]
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，基层就业分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 定义学历枚举
        educations = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        
        # 4. 查询各学历及总体的基层就业分布
        results = []
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        
        # 4.1 查询总体数据
        total_sql = f"""
        SELECT 
            country_base_project, 
            COUNT(*) as count,
            COUNT(*)/(SELECT COUNT(*) 
                    FROM {student_table} 
                    WHERE shard_tb_key = %s 
                    AND country_base_project != ''
                    AND employment_status NOT IN ('待就业', '暂不就业')) as ratio
        FROM {student_table}
        WHERE 
            shard_tb_key = %s
            AND country_base_project != ''
            AND employment_status NOT IN ('待就业', '暂不就业')
        GROUP BY country_base_project
        ORDER BY ratio DESC
        """
        total_results = db.fetchall(total_sql, (shard_tb_key, shard_tb_key))
        
        if total_results:
            total_str = ""
            for i, item in enumerate(total_results):
                if i < 2:  # 只取前两名
                    percent = round(float(item['ratio']) * 100, 2)
                    total_str += f"{item['country_base_project']}，占比为{percent}%；"
                    if i == 0:
                        total_str += "其次为"
            results.append({
                "key": "总体",
                "val": total_str.rstrip("；其次为")
            })
        
        # 4.2 查询各学历数据
        for edu in educations:
            edu_sql = f"""
            SELECT 
                country_base_project, 
                COUNT(*) as count,
                COUNT(*)/(SELECT COUNT(*) 
                        FROM {student_table} 
                        WHERE shard_tb_key = %s 
                        AND education = %s
                        AND country_base_project != ''
                        AND employment_status NOT IN ('待就业', '暂不就业')) as ratio
            FROM {student_table}
            WHERE 
                shard_tb_key = %s
                AND education = %s
                AND country_base_project != ''
                AND employment_status NOT IN ('待就业', '暂不就业')
            GROUP BY country_base_project
            ORDER BY ratio DESC
            """
            edu_results = db.fetchall(edu_sql, (shard_tb_key, edu, shard_tb_key, edu))
            
            if edu_results:
                edu_str = ""
                for i, item in enumerate(edu_results):
                    if i < 2:  # 只取前两名
                        percent = round(float(item['ratio']) * 100, 2)
                        edu_str += f"{item['country_base_project']}，占比为{percent}%；"
                        if i == 0:
                            edu_str += "其次为"
                results.append({
                    "key": edu,
                    "val": edu_str.rstrip("；其次为")
                })

        logger.info(f"指标 '各学历及总体，基层就业分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": [{
                "calcVals": [{
                    "val": results
                }]
            }]
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，基层就业分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，基层就业分布",
            "code": 500,
            "error": str(e)
        }